#pragma once
#include <mysql.h>
#include "DataTypes.h"

#pragma comment(lib,"libmysql.lib")


class CMySQLEx
{
public:
	CMySQLEx(void);
	~CMySQLEx(void);

	void SetConnection(LPCTSTR lpszHostName, LPCTSTR lpszDatabase, LPCTSTR lpszUserName, LPCTSTR lpszPassword);
	void SetExternalConnection(LPCTSTR lpszHostName, LPCTSTR lpszDatabase, LPCTSTR lpszUserName, LPCTSTR lpszPassword);

	BOOL UpdateDatabase(void);

	CString GetSQLString(char* szText);
	ULONG GetSQLULong(char* szText);
	LONG GetSQLLong(char* szText);
	DOUBLE GetSQLDouble(char* szText);
	INT GetSQLInt(char* szText);

	CString ES(CString String);

	CString GetTypeTable(INT nType);	

	CString GetMessageSubject(INT nType, LONG nID);
	CString GetDescription(INT nType, LONG nID);
	ULONG GetDescriptions(INT nType, CStringArray* psaDescriptions, INT nFormat=-1, CArray<LONG>* pnaID=NULL);	

	ULONG GetRequestCount(CString szFilter);
	ULONG GetRowCount(INT nType, LONGLONG nID);
	ULONG GetRowCount(INT nType, CString szField, CString szValue);

	BOOL RemoveRow(INT nType, LONGLONG nID);
	BOOL RemoveRows(INT nType, CString szField, LONGLONG nID, CString szFilter=_T(""));
	BOOL SetCurrentDateTime(INT nType, CString szField, LONGLONG nID);

	BOOL SetFieldValue(INT nType, CString szField, LONGLONG nID, LONG nValue);
	BOOL SetFieldValue(INT nType, CString szField, LONGLONG nID, CString szValue);
	BOOL UpdateFieldValue(INT nType, CString szField, LONG nValue, LONG nNew);
	BOOL UpdateFieldValue(INT nType, CString szField, CString szValue, CString szNew);

	BOOL GetInformation(INT nType, CString* szHTML, BOOL* bUserListed);
	BOOL SetInformation(INT nType, CString szHTML, BOOL bUserListed);

	BOOL UpdateUserHistory(LONGLONG nID, CString szDescription);
	BOOL UpdateRequestHistory(LONGLONG nRID, LONGLONG nUID, LONG nStatus, CString szDescription);

	LONG UpdateClaimLimit(INT nHours);

	BOOL GetUserHistory(USER_HISTORY *pHistory, LONGLONG nID);
	BOOL GetRequestHistory(REQUEST_HISTORY *pHistory, LONGLONG nID);
	BOOL GetRequestHistorys(CArray<REQUEST_HISTORY> *pHistory, LONGLONG nID, CString szSort = _T(""));
	BOOL GetRequestHistorys(CArray<REQUEST_HISTORY> *pHistory, CString szFilter = _T(""), CString szSort = _T(""));

	BOOL GetRequestLinkSources(CStringArray *pSources);
	BOOL GetRequestLinks(CArray<REQUEST_LINK> *pLinks, CString szUID);
	BOOL GetRequestLinkss(CArray<REQUEST_LINK> *pLinks, CString szFilter = _T(""), CString szSort = _T(""));
	BOOL SetRequestLinks(REQUEST_LINK *pLinks, LONG nID=-1, BOOL bInsert=TRUE);

	BOOL GetSourceLinkSources(CStringArray *pSources);
	BOOL GetSourceLink(SOURCE_LINK *pLink, LONG nID);
	BOOL GetSourceLink(SOURCE_LINK *pLink, CString szField, CString szFilter);
	BOOL GetSourceLinks(CArray<SOURCE_LINK> *pLinks, CString szFilter = _T(""), CString szSort = _T(""));
	BOOL SetSourceLink(SOURCE_LINK *pLink, LONG nID=-1, BOOL bInsert=TRUE);

	BOOL GetDescriptionCollection(COLLECTION_LOOKUP *pCollection, LONG nID);
	BOOL GetDescriptionCollections(CArray<COLLECTION_LOOKUP> *pCollection, LPCTSTR lpszSort=NULL);
	BOOL SetDescriptionCollection(COLLECTION_LOOKUP *pCollection, LONG nID=-1, BOOL bInsert=FALSE);

	BOOL GetDescriptionUserType(USER_TYPE_LOOKUP *pUserType, LONG nID);
	BOOL SetDescriptionUserType(USER_TYPE_LOOKUP *pUserType, LONG nID=-1, BOOL bInsert=FALSE);

	BOOL GetDescriptionILL(ILL_LOOKUP *pILL, LONG nID);
	BOOL GetDescriptionILLs(CArray<ILL_LOOKUP> *pILL, LPCTSTR lpszSort=NULL);
	BOOL SetDescriptionILL(ILL_LOOKUP *pILL, LONG nID=-1, BOOL bInsert=FALSE);

	BOOL GetDescriptionMethod(METHOD_LOOKUP *pMethod, LONG nID);
	BOOL GetDescriptionMethods(CArray<METHOD_LOOKUP> *pMethod, LPCTSTR lpszSort=NULL);
	BOOL SetDescriptionMethod(METHOD_LOOKUP *pMethod, LONG nID=-1, BOOL bInsert=FALSE);

	BOOL GetDescriptionPriority(PRIORITY_LOOKUP *pPriority, LONG nID);
	BOOL GetDescriptionPrioritys(CArray<PRIORITY_LOOKUP> *pPriority, LPCTSTR lpszSort=NULL);
	BOOL SetDescriptionPriority(PRIORITY_LOOKUP *pPriority, LONG nID=-1, BOOL bInsert=FALSE);

	BOOL GetDescriptionStatus(STATUS_LOOKUP *pStatus, LONG nID);
	BOOL GetDescriptionStatuss(CArray<STATUS_LOOKUP> *pStatus, LPCTSTR lpszSort=NULL);
	BOOL SetDescriptionStatus(STATUS_LOOKUP *pStatus, LONG nID=-1, BOOL bInsert=FALSE);

	BOOL GetDescriptionLibrary(LIBRARY_LOOKUP *pLibrary, LONG nID);
	BOOL GetDescriptionLibrarys(CArray<LIBRARY_LOOKUP> *pLibrary, LPCTSTR lpszSort=NULL);
	BOOL GetDescriptionLibraryList(CArray<LIBRARY_LOOKUP> *pLibrary, LPCTSTR lpszSort=NULL);
	BOOL SetDescriptionLibrary(LIBRARY_LOOKUP *pLibrary, LONG nID=-1, BOOL bInsert=FALSE);

	BOOL GetDescriptionStaff(STAFF_LOOKUP *pStaff, LONGLONG nID);
	BOOL GetDescriptionStaffs(CArray<STAFF_LOOKUP> *pStaff, LPCTSTR lpszSort=NULL, LPCTSTR lpszFilter=NULL, BOOL bSetAt=TRUE);
	BOOL GetStaffSet(STAFF_SET *pStaff, LONGLONG nID);
	BOOL GetStaffSet(STAFF_SET *pStaff, CString szName);
	BOOL GetStaffSet(CArray<STAFF_SET> *pStaff, CString szFilter, CString szSort = _T(""));
	BOOL SetStaffSet(STAFF_SET *pStaff, LONGLONG nID=-1, BOOL bInsert=FALSE);
	
	void InitStaffSet(STAFF_SET *pStaff);
	void InitUserSet(USER_SET *pUser);
	void InitRequestSet(REQUEST_SET *pRequest);

	BOOL GetDescriptionUser(USER_LOOKUP *pUser, LONGLONG nID);
	BOOL GetDescriptionUsers(CArray<USER_LOOKUP> *pUser, LPCTSTR lpszSort=NULL, LPCTSTR lpszFilter=NULL, BOOL bSetAt=TRUE);
	BOOL GetUserSet(CArray<USER_SET> *pUser);
	BOOL GetUserSet(CArray<USER_SET> *pUser, CString szFilter, CString szSort = _T(""));
	BOOL GetUserSet(USER_SET *pUser, LONGLONG nID);
	BOOL GetUserSet(USER_SET *pUser, CString szEmail);
	BOOL SetUserSet(USER_SET *pUser, LONGLONG nID=-1, BOOL bInsert=FALSE);
	BOOL SetUserSet(LDAP_USER_INFO *pUser, BOOL bInsert=TRUE, LONGLONG *pnID=NULL);
	BOOL SetExternalUserSet(USER_SET *pUser, LONGLONG nID);

	BOOL GetExternalUserID(USER_SET *pUser, CString szEmail);

	BOOL GetExternIllSet(CArray<EXTERNAL_ILL_SET> *pIll, CString szFilter = _T(""), CString szSort = _T(""));
	BOOL GetExternIllSet(EXTERNAL_ILL_SET *pIll, CString szEmail);
	BOOL SetExternIllSet(EXTERNAL_ILL_SET *pIll, CString szEmail, BOOL bInsert=FALSE);
	BOOL SetExternIllSet(EXTERNAL_ILL_SET *pIll, LONGLONG nID);
	
	BOOL GetExternIllHistory(EXTERNAL_ILL_HISTORY *pIll, LONGLONG nID);
	BOOL GetExternIllHistorys(CArray<EXTERNAL_ILL_HISTORY> *pIll, CString szFilter = _T(""), CString szSort=_T(""));
	BOOL UpdateExternIllHistory(LONGLONG nStaffID, LONGLONG nID, CString szDescription, CString szLink);

	BOOL GetEmptyRequest(REQUEST_SET *pRequest);
	ULONG GetRequestSet(CArray<REQUEST_SET> *pRequest);
	ULONG GetRequestSet(CArray<REQUEST_SET> *pRequest, LPCTSTR lpszSort=NULL, LPCTSTR lpszFilter=NULL, LPCTSTR lpszSearch=NULL, LONG nLimit=-1, LONG nRange=-1);

	BOOL GetRequestSet(REQUEST_SET *pRequest, LONGLONG nID);
	BOOL GetRequestSet(REQUEST_SET *pRequest, CString szEmail);
	BOOL SetRequestSet(REQUEST_SET *pRequest, LONGLONG nID=-1, BOOL bInsert=FALSE);
	BOOL SetExternalRequestSet(REQUEST_SET *pRequest, LONGLONG nID=-1, BOOL bInsert=FALSE);
	BOOL RemoveRequest(LONGLONG nID);

	BOOL SetFullRequest(ARTICLE_REQUEST *pRequest);
	BOOL GetFullRequest(ARTICLE_REQUEST *pRequest, LONGLONG nID);

	ULONG GetRequestDisplaySet(CArray<REQUEST_DISPLAY_SET> *pRequest, LPCTSTR lpszSort=NULL, LPCTSTR lpszFilter=NULL, LPCTSTR lpszSearch=NULL, LONG nLimit=0, LONG nRange=0);

	ULONG GetFullRequestMap(RequestMap *pRequestSet, LPCTSTR lpszSort=NULL, LPCTSTR lpszFilter=NULL, LONG nLimit=0, LONG nRange=0);
	ULONG AddFullRequestMap(RequestMap *pRequestSet, LPCTSTR lpszSort=NULL, LPCTSTR lpszFilter=NULL, LONG nLimit=0, LONG nRange=0);

public:
	enum RESULT_TYPE
	{
		collection = 0,
		method,
		priority,
		status,
		library,
		ill,
		referrals,
		copyright,
		user_type,
		staff,
		users,
		requests,
		external_ill,
		external_ill_history,
		request_links,
		source_links
	};

	enum FORMAT_TYPE
	{
		list = 0,
		list_ILL,
		list_iILL,
		list_eILL,
	};

private:
	BOOL Connect(BOOL bExternal=FALSE);
	void Close();
	void GetFieldNames(MYSQL_RES *res);
	LONG IndexByName(MYSQL_RES *res, LPCTSTR pName, LPCTSTR pTable=NULL);
	MYSQL_RES* QueryForRow(LPCTSTR lpszQuery, MYSQL_ROW *row);

private:
	MYSQL m_mysql;

	CString m_szHostName;
	CString m_szDatabase;
	CString m_szUserName;
	CString m_szPassword;

	CString m_szHostNameExternal;
	CString m_szDatabaseExternal;
	CString m_szUserNameExternal;
	CString m_szPasswordExternal;

	CStringArray m_sFields;
};
